package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.PersonDao;
import entity.Person;

public class PersonDaoImpl extends BaseDao implements PersonDao {
	
	Connection conn=null;//数据库连接对象
	PreparedStatement pstmt=null;//预编译sql命令执行对象
	ResultSet rs=null;//结果集对象

	/**
	 * 查询所有的人员信息按照编号降序排列，并返回查询结果
	 */
	@Override
	public List<Person> personAllStuInfo() {
		// TODO Auto-generated method stub
		List<Person> list=new ArrayList<Person>();
		
		try {
			//获取数据库连接
			conn = super.getConn();
			//查询语句
			String sql="SELECT id,name,sex,age,rank,department FROM person ORDER BY id DESC";
			//实例化预编译sql命令执行对象
			pstmt = conn.prepareStatement(sql);
			//执行命令
			rs = pstmt.executeQuery();
			
			Person per = null;
			
			//读取数据
			while(rs.next()) {
				per = new Person();
				per.setId(rs.getInt("id"));
				per.setName(rs.getString("name"));
				per.setSex(rs.getString("sex"));
				per.setAge(rs.getInt("age"));
				per.setRank(rs.getString("rank"));
				per.setDepartment(rs.getString("department"));
				
				list.add(per);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			//释放资源 //点出来什么用什么
			super.closeAll(conn, pstmt, rs);
		}
		
		return null;
	}

	
	/**
	 * 添加人员信息,将新的人员信息保存到数据库
	 */
	@Override
	public int addPerson(Person person) {
		// TODO Auto-generated method stub
		
		//查询语句
		String sql="INSERT INTO person (name,sex,age,rank,department) VALUES (?,?,?,?,?)";
		
		try {
			conn = super.getConn();
			pstmt=conn.prepareStatement(sql);
			//pstmt.setString(1, keyword.getName());

			return pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{ 
			super.closeAll(conn, pstmt, null);
		}
		return 0;
		
	}

	
	/**
	 * 删除人员信息,将指定的人员从数据库中删除
	 */
	@Override
	public int delPerson(int perid) {
		// TODO Auto-generated method stub
		String sql="delete from person where id=?";
		try {
			conn = super.getConn();
			pstmt=conn.prepareStatement(sql);
			
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{ 
			super.closeAll(conn, pstmt, null);
		}
		return 0;
	}

}
